# coding:utf8
import pymysql
import yaml
from Common.Logger import path


class MysqlTest:

    def __mysql_init(self):
        # 打开yaml配置文件
        with open(path + '\lib\confmysql.yaml', mode='r', encoding="utf-8") as file:
            self.conf = yaml.load(stream=file, Loader=yaml.FullLoader)['test_pos3']
        # 连接数据库
        connect = pymysql.connect(**self.conf)
        return connect

    def mysql_execute(self, sql):
        # 获取游标
        connect = self.__mysql_init()
        cursor = connect.cursor()
        cursor.execute(sql)
        # 获取表头字段
        dec = cursor.description
        # 获取所有的查询结果
        result = cursor.fetchall()
        # 表头字段存储到列表中
        list_dec = []
        # 读取到的结果数据存储列表中
        list_result = []
        # 遍历表头字段，存储到列表中
        for i in range(0, len(dec)):
            list_dec.append(dec[i][0])
        # 遍历结果的每条数据
        for j in range(0, len(result)):
            # 遍历完一条数据，重置map
            map = {}
            # 遍历每个数据
            for x in range(0, len(result[j])):
                # 存储到字典中
                map[list_dec[x]] = str(result[j][x])
            # 字典添加到列表中
            list_result.append(map)
        # 关闭游标和连接
        cursor.close()
        connect.close()
        return list_result


if __name__ == '__main__':
    mytest = MysqlTest()

    aa = mytest.mysql_execute('select * from order_detail  where store_id="1550313489139277827" and order_status_id="95";')
    print(aa[0].get("order_id"))

    # a = mytest.mysqlTest(
    #     "SELECT d.customer_name '客户',d.store_name '门店',d.serial_number '设备编号' FROM dc_device d WHERE d.customer_name = '广州卓思道酒店管理有限公司从化分公司';")
    # # print(type(a))
    # print(a)
    # print(a[0])
    # print(a[0].get("客户"))

    # b = mytest.mysqlTest(
    #     "select d.customer_name as '客户',d.customer_flag '模式',t.income_date as '日期',sum(t.fact_amount) as '营业额' from dc_device_income t inner join dc_device d on t.device_serial_number=d.serial_number where d.customer_name='广州温宁明思设计有限公司' and t.income_date >= '2022-06-15' and t.income_date <= '2022-06-15' GROUP BY d.customer_name,t.income_date,d.customer_flag order by d.customer_name,t.income_date,d.customer_flag;")
    # print(b)
    # print(str(b[0].get('营业额')))

    # 营业额
    # e = mytest.mysqlTest(
    #     "select d.customer_name as '客户',d.customer_flag as '模式(1:合营 0:其他)',d.store_name as '门店',d.name as '设备',t.device_serial_number as '设备编号',sum(t.income_amount) as '营业额',sum(t.refund_amount) as '退款金额',sum(t.fact_amount) as '实收金额',sum(t.order_count) as '订单数',sum(t.product_quantity) as '菜品数量' from dc_device_income t inner join dc_device d on t.device_serial_number=d.serial_number where d.customer_name='广州卓思道酒店管理有限公司从化分公司' and t.income_date >= '2022-06-15' and t.income_date <= '2022-06-15' GROUP BY d.customer_name,d.customer_flag,d.store_name,d.name,t.device_serial_number;")
    # print(e)
    # print(e[0])
    # ll = e[0].get('营业额')
    # print(ll)
    # print(type(ll))
    # ll =ll[:ll.find('.')+2]
    # print(ll)

    # c = mytest.mysql_execute("SELECT * FROM `userinfo` where username = 'Will';")
    # print(c)
    # print(c[0].get('nickname'))
